#! /usr/bin/python
# Copyright (C) 2010  Johan Jordaan (www.johanjordaan.co.za)
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

# Todo :
# 1) Handle potential duplicate cards in a set

import sys
sys.path.append('..\pylibs')
sys.path.append('..\pycommon')
import os
import os.path
from optparse import OptionParser
import sqlite3

import unicodecsv
import filemappings as FM
import dbmappings as DB
from constants import *

inserted_sets = []
inserted_cards = []

def insert_set_into_db(c,set_name):
  if set_name not in inserted_sets:
    print 'Inserting [%s]'%(set_name)
    inserted_sets.append(set_name)
    c.execute('insert into sets values (?)',(set_name,))

def insert_card_into_db(c,set_name,card):
  if card[NAME] not in inserted_cards:
    inserted_cards.append(card[NAME])
    c.execute('insert into cards (%s) values (%s)'%(','.join(DB.CARDS.values()),','.join(['\''+card[k].replace('\'','\'\'')+'\'' for k in DB.CARDS.keys()])))

  c.execute('insert into setcards values(?,?,?)',(set_name,card[NAME],card[RARITY],))

def insert_sets_into_db(c,input_root):  
  # Itterate over all the files in the enriched csv directory
  #
  for file_name in os.listdir(input_root):
    if file_name.endswith('.csv'):
      f = open(os.path.join(input_root,file_name))
      r = unicodecsv.UnicodeReader(f)

      i = 0
      for row in r:
        if i == 0:              # The set name
          set_name = row[0]
          insert_set_into_db(c,set_name)
        elif i == 1:            # The headers/keys
          keys = row
        else:                   # Everything else is data
          card_dict = {}
          for col in range(len(row)):
            # Read each item and assign them to the relevant independent keys
            #
            card_dict[FM.ECSV.key_for(keys[col])] = row[col]
          
          insert_card_into_db(c,set_name,card_dict)  
        i = i + 1  
      
      f.close()

def create_db_table(c,table_name,table_mapping,index_mapping=None):
  c.execute('create table if not exists %s (%s)'%(table_name,','.join(table_mapping.values())))
  c.execute('delete from %s'%(table_name)) 
  if index_mapping != None:
    c.execute('create index if not exists %s_index on %s (%s)'%(table_name,table_name,','.join(index_mapping.values())))
      
      
def create_db_structure(c):
  create_db_table(c,'sets',DB.SETS)
  create_db_table(c,'cards',DB.CARDS,DB.CARDS_INDEX)
  create_db_table(c,'setcards',DB.SETCARDS,DB.SETCARDS_INDEX)
  create_db_table(c,'types',DB.TYPES)
  create_db_table(c,'subtypes',DB.SUBTYPES)
  create_db_table(c,'supertypes',DB.SUPERTYPES)
  create_db_table(c,'cardtypes',DB.CARDTYPES)
  create_db_table(c,'cardsubtypes',DB.CARDSUBTYPES)
  create_db_table(c,'cardsupertypes',DB.CARDSUPERTYPES)
  
def create_db(input_root,output_root,dbfile_name,force_db):      
  
  db_file_path = os.path.join(output_root,dbfile_name)
  if os.path.exists(db_file_path) and force_db:
    os.remove(db_file_path)
  
  conn = sqlite3.connect(os.path.join(output_root,dbfile_name))
  c = conn.cursor()

  create_db_structure(c)
  insert_sets_into_db(c,input_root)
  
  conn.commit()
    
      
      
if __name__ == '__main__':
  parser = OptionParser()
  parser.add_option('-f', '--dbfile',dest='dbfile_name',default='mtgt_master.s3db',help='File name for the database file')
  parser.add_option('-o', '--output',dest='output_root',default='..\data\sqlite',help='Root directory for the output database file')
  parser.add_option('-i', '--input',dest='input_root',default='..\data\enriched\csv',help='Root directory for the input files')
  parser.add_option('-X', action='store_true', dest='force_db',default=False,help='Force the deletion of the db if it exists')
   
  (options, args) = parser.parse_args()
  
  create_db(options.input_root,options.output_root,options.dbfile_name,options.force_db)

